/**
 * Author: YuBing 羽冰
 * time: 2021/5/22
 * discription: 用于操控mysql数据库
 * dependence module: mysql
 */

var sql = require("mysql");
// connection连接时，释放连接的方式
var ReleaseType = "release";
// mysql实例
var sqldb;

// 连接池连接
function sqlLink(dbMsg) {
  // 最大链接数，默认10
  (!dbMsg.connectionLimit) && (dbMsg.connectionLimit = 10);
  // 创建连接池
  try {
    sqldb = sql.createPool(dbMsg)
    if (sqldb) console.log("mysql 对象创建成功");
  } catch (err) {
    console.log("mysql 对象创建失败");
    console.log(err);
  }
}

// 内部使用,用于拼接条件 
function setCondition(conditionSetting) {
  var str = "";
  if (Array.isArray(conditionSetting)) {
    // 遍历拼接
    conditionSetting.forEach(function (item) {
      // 跳过空对象
      if (JSON.stringify(item) === "{}") return;
      for (var prop in item) {
        if (item[prop].constructor === Object) {
          var propSetting = item[prop];
          // [{a:{value:b,kind:">="、"="、"<="、"!="、"not like"、"like"}}]
          // --> a >= b   a = b   a <= b  a not like b ...
          // 防止忘记定义kind
          propSetting.kind = propSetting.kind || "=";
          str += prop + " " + propSetting.kind + " " + propSetting.value + " or ";
          continue;
        }
        // [{a:b},{e:f}] --> "a=b or e=f"
        str += prop + "=" + item[prop] + " or ";
      }
    })
    // 去除末尾的 or
    str = str.substring(0, str.length - 4);
  }
  else if (conditionSetting && conditionSetting.constructor === Object) {
    for (var props in conditionSetting) {
      if (conditionSetting[props].constructor === Object) {
        var propsSetting = conditionSetting[props];
        // {a:{value:b,kind:">="、"="、"<="、"!="、"not like"、"like"}} 
        // --> a >= b   a = b   a <= b  a not like b ...
        // 防止忘记定义kind
        propsSetting.kind = propsSetting.kind || "=";
        str += props + " " + propsSetting.kind + " " + propsSetting.value + " and ";
        continue;
      }
      // {a:b,c:d} --> "a=b and c=d"
      str += props + "=" + conditionSetting[props] + " and ";
    }
    // 去除末尾的 and
    str = str.substring(0, str.length - 5);
  }
  else {
    // 想自己写语句的
    str = conditionSetting;
  }
  return str;
}

// 增
function insert(sqlMsg, Linktype) {
  if (!sqlMsg.table || !sqlMsg.value) {
    console.log("Enter right sqlMsg");
    return;
  }
  // 插入值拼接
  sqlMsg.value = sqlMsg.value.join(",");
  // 拼接sql语句
  var sql = "INSERT INTO " + sqlMsg.table + " values (" + sqlMsg.value + ")";
  return getData(sql, Linktype);
}

// 删
function move(sqlMsg, Linktype) {
  if (!sqlMsg.table || !sqlMsg.condition) {
    console.log("Enter right sqlMsg");
    return;
  }
  // 条件拼接
  var condition = setCondition(sqlMsg.condition);
  var sql = "delete from " + sqlMsg.table + " where " + condition;
  return getData(sql, Linktype);
}

// 查
function select(sqlMsg, Linktype) {
  if (!sqlMsg.table) {
    console.log("Enter right sqlMsg");
    return;
  }
  let {
    table, target = ["*"],
    condition, orderBy = {},
    groupBy, having, limit,
    count = false
  } = sqlMsg;
  // 查询表拼接处理(如果想多表连接的情况下)
  if (Array.isArray(table)) table = table.join(",");
  // 查询列名拼接
  if (Array.isArray(target)) target = target.join(",");
  // 基础语句拼接
  var sql = "SELECT " + target + " FROM " + table;
  // 条件拼接
  if (condition && JSON.stringify(condition) !== "{}"
    && JSON.stringify(condition) !== "[]") {
    // 空对象或空数组拒绝进入
    condition = setCondition(condition);
    sql += " WHERE " + condition;
  }
  // 排序拼接
  if (typeof orderBy === "string") {
    if (/order by/g.test(orderBy) || /ORDER BY/g.test(orderBy)) {
      // 想自己写语句的
      // orderBy:"order by XXX"
      sql += orderBy;
      return;
    }
    // orderBy: "XX" 且里面没有order by关键字
    sql += " ORDER BY " + orderBy + " ASC";
  }
  else if (orderBy.constructor === Object && orderBy.field
    && (!orderBy.by || orderBy.by.toUpperCase() === "ASC")) {
    // orderBy: {field:"XX"}  / orderBy: {field:"XX", by:"ASC"}
    sql += " ORDER BY " + orderBy.field + " ASC";
  }
  else if (orderBy.constructor === Object && orderBy.field
    && orderBy.by && orderBy.by.toUpperCase() === "DESC") {
    // orderBy: {field:"XX", by:"DESC"}
    sql += " ORDER BY " + orderBy.field + " DESC";
  }
  // 分组拼接
  // groupBy:"xx"
  if (groupBy) sql += " GROUP BY " + groupBy;
  // 分组筛选HAVING拼接,要先进行分组才能使用HAVING
  if (groupBy && having) {
    having = setCondition(having);
    sql += " HAVING " + having;
  }
  // 查询限制拼接(一般用于分页)
  if (limit) {
    (limit.constructor === String) && (sql += " LIMIT " + limit);
    (limit.constructor === Array) && (sql += " LIMIT " + limit.join(","));
  }
  if (count) {
    var countSql = "SELECT COUNT(*) FROM (" + sql + ") a";
    let promise =
      Promise.all([getData(countSql, Linktype), getData(sql, Linktype)])
        .then(function (result) {
          // 把复杂的查询结果结构简单化一下
          return (result = [result[0][0]["COUNT(*)"], result[1]]);
        });

    return promise;
  }
  return getData(sql, Linktype);
}

// 改
function update(sqlMsg, Linktype) {
  if (!sqlMsg.table || !sqlMsg.update || !sqlMsg.condition) {
    console.log("Enter right sqlMsg");
    return;
  }
  // 更新值拼接 {a:b,c:d} --> update a=b,c=d,...
  var update = "";
  for (var prop in sqlMsg.update) {
    update += prop + "=" + sqlMsg.update[prop] + ",";
  }
  // 去除末尾的逗号
  update = update.substring(0, update.length - 1);
  // 条件拼接
  var condition = setCondition(sqlMsg.condition);
  var sql = "UPDATE " + sqlMsg.table + " SET " + update + " WHERE " + condition;
  return getData(sql, Linktype);
}

// 关闭连接池 
function end() {
  if (!sqldb) return;
  // 关闭连接池
  sqldb.end();
}

// 管理查询方式
function getData(sql, Linktype) {
  if (!sqldb) {
    console.log("Please Link Mysql First!!");
    return;
  }
  // 默认使用query方式进行查询
  if (!Linktype) {
    return new Promise(function (resolve, reject) {
      queryData(sql, resolve, reject)
    })
  }
  else {
    return new Promise(function (resolve, reject) {
      connectionData(sql, resolve, reject)
    })
  };
}

// query查询数据库
function queryData(sql, resolve, reject) {
  try {
    sqldb.query(sql, function (err, result) {
      if (err) {
        reject(err);
        return;
      }
      // 获取数据
      resolve(result);
    });
  } catch (err) {
    reject(err);
  }
}

// 获取连接池连接查询数据库
function connectionData(sql, resolve, reject) {
  // 开始获取数据
  try {
    sqldb.getConnection(function (err, connection) {
      if (err) {
        reject(err);
        return;
      };
      connection.query(sql, function (error, result) {
        if (error) {
          reject(err);
          return;
        };
        // 获取数据
        resolve(result);
        // 使用完连接后进行释放
        if (ReleaseType === "destroy") connection.destroy();
        else connection.release();
      });
    });
  } catch (err) {
    reject(err);
  }
}

module.exports = {
  creatLink: function (dbMsg, releaseType) {
    ReleaseType = releaseType || ReleaseType;
    sqlLink(dbMsg);
  },
  insert,
  select,
  delete: move,
  update,
  end,
}